using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Workflows.Components;

namespace Workflows.Components.DB.Sql
{
    public partial class SqlEditor : Form, IComponentEditor  
    {
        private object _OwnerActivityNode;
        private SqlComponent _SqlComponent;
        /// <summary></summary>
        public SqlEditor()
        {
            InitializeComponent();
        }

        private void SqlEditor_Load(object sender, EventArgs e)
        {
            this.cbo_CommandType.Items.Clear();
            this.cbo_CommandType.Items.Add("Text");
            this.cbo_CommandType.Items.Add("Stored procedure");
            this.cbo_CommandType.SelectedIndex = 0;

            this.cbo_QueryOutputType.Items.Clear();
            this.cbo_QueryOutputType.Items.Add("Null");
            this.cbo_QueryOutputType.Items.Add("Scalar");
            this.cbo_QueryOutputType.Items.Add("Table");
            this.cbo_QueryOutputType.SelectedIndex = 0;

            this.cbo_QueryOutputDataType.Items.Clear();
            for (int i = 0; i < 5; i++)
            {
                SimpleDataType gvarDbType = (SimpleDataType) i;
                this.cbo_QueryOutputDataType.Items.Add(gvarDbType.ToString());
            }
            this.InitDbTypeComboBox();
            this.InitColumnDbTypeComboBox();
        }

        private void InitDbTypeComboBox()
        {
            foreach(DataGridViewRow dr in this.dv_Parameters.Rows)
            {
                if(dr.IsNewRow)
                {
                    DataGridViewComboBoxCell cellDbType = (DataGridViewComboBoxCell) dr.Cells[1];
                    for (int i = 0; i < 24; i++)
                    {
                        DbType dbType = (DbType) i;
                        cellDbType.Items.Add(dbType.ToString());
                    }
                }
            }
        }

        private void InitColumnDbTypeComboBox()
        {
            foreach (DataGridViewRow dr in this.dv_TableDefinition.Rows)
            {
                if (dr.IsNewRow)
                {
                    DataGridViewComboBoxCell cellDbType = (DataGridViewComboBoxCell)dr.Cells[1];
                    for (int i = 0; i < 24; i++)
                    {
                        DbType dbType = (DbType)i;
                        cellDbType.Items.Add(dbType.ToString());
                    }
                }
            }
        }

        public event UpdateComponentSettings UpdateComponentSettingEvent;

        public object OwnerActivityNode
        {
            get { return _OwnerActivityNode; }
            set { _OwnerActivityNode = value; }
        }

        public Type WorkflowComponentType
        {
            get { return typeof(SqlComponent); }
        }

        public void LoadSettings(
            GlobalVariable[] globalVars,
            WorkflowComponentBase[] fromComponents, 
            object activityNode, WorkflowComponentBase wfComponent)
        {
            this._OwnerActivityNode = activityNode;
            this._SqlComponent = (SqlComponent) wfComponent;

            this.txt_ConnStr.Text = string.IsNullOrEmpty(this._SqlComponent.ConnStr)
                                        ? string.Empty
                                        :
                                            this._SqlComponent.ConnStr;
            this.txt_CmdText.Text = string.IsNullOrEmpty(this._SqlComponent.SqlCommandText)
                                        ? string.Empty
                                        : this._SqlComponent.SqlCommandText;
            this.cbo_CommandType.SelectedIndex = 0;
            if (this._SqlComponent.SqlCommandType == CommandType.StoredProcedure)
                this.cbo_CommandType.SelectedIndex = 1;

            this.dv_Parameters.Rows.Clear();
            if(this._SqlComponent.Parameters !=null && this._SqlComponent.Parameters.Length>0)
            {
                foreach (SimpleSqlParameter sqlParam in this._SqlComponent.Parameters)
                {
                    DataGridViewRow dr=new DataGridViewRow();
                    DataGridViewTextBoxCell cellName=new DataGridViewTextBoxCell();
                    cellName.Value = sqlParam.ParameterName;
                    dr.Cells.Add(cellName);

                    DataGridViewComboBoxCell cellDbType=new DataGridViewComboBoxCell();
                    for(int i=0;i<=24;i++)
                    {
                        DbType dbType = (DbType) i;
                        cellDbType.Items.Add(dbType.ToString());
                    }
                    cellDbType.Value = sqlParam.DbType.ToString();
                    dr.Cells.Add(cellDbType);

                    DataGridViewCheckBoxCell cellReturn=new DataGridViewCheckBoxCell();
                    cellReturn.Value = false;
                    if(sqlParam.Direction !=ParameterDirection.Input)
                    {
                        cellReturn.Value = true;
                    }
                    dr.Cells.Add(cellReturn);

                    DataGridViewTextBoxCell cellValue=new DataGridViewTextBoxCell();
                    if (sqlParam.Value == null || sqlParam.Value is DBNull)
                    {
                        cellValue.Value = string.Empty;
                    }
                    else
                    {
                        string paramValue = sqlParam.Value.ToString();
                        cellValue.Value = SqlDbTypeUtil.GetValueFromString(paramValue, sqlParam.DbType);
                    }
                    dr.Cells.Add(cellValue);

                    this.dv_Parameters.Rows.Add(dr);
                }
            }

            this.cbo_QueryOutputType.SelectedIndex = (int) this._SqlComponent.QueryOutputType;
            this.cbo_QueryOutputDataType.SelectedIndex = (int) this._SqlComponent.QueryScalarOutputType;
            this.dv_TableDefinition.Rows.Clear();
            if(this._SqlComponent.QueryOutputTableDefinition !=null)
            {
                foreach(DataColumn col in this._SqlComponent.QueryOutputTableDefinition.Columns)
                {
                    DataGridViewRow dr=new DataGridViewRow();
                    
                    DataGridViewTextBoxCell cellColName=new DataGridViewTextBoxCell();
                    cellColName.Value = col.ColumnName;
                    dr.Cells.Add(cellColName);

                    DataGridViewComboBoxCell cellDbType=new DataGridViewComboBoxCell();
                    for(int i=0;i<24;i++)
                    {
                        DbType dbtype = (DbType) i;
                        cellDbType.Items.Add(dbtype.ToString());
                    }
                    cellDbType.Value = SqlDbTypeUtil.ToDbType(col.DataType);
                    dr.Cells.Add(cellDbType);

                    this.dv_TableDefinition.Rows.Add(dr);
                }
            }
        }

        public TestResult Test()
        {
            return new TestResult(true, string.Empty);
        }

        public void SaveSettings()
        {
            this._SqlComponent.ConnStr = this.txt_ConnStr.Text;
            this._SqlComponent.SqlCommandText = this.txt_CmdText.Text;
            this._SqlComponent.SqlCommandType = CommandType.Text;
            if (this.cbo_CommandType.SelectedIndex == 1)
                this._SqlComponent.SqlCommandType = CommandType.StoredProcedure;

            List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
            if(this.dv_Parameters.Rows.Count>0)
            {
                foreach(DataGridViewRow dr in this.dv_Parameters.Rows)
                {
                    if(!dr.IsNewRow)
                    {
                        SimpleSqlParameter sqlParam = new SimpleSqlParameter();
                        sqlParam.ParameterName = dr.Cells[0].Value.ToString();

                        DataGridViewComboBoxCell cellDbType = (DataGridViewComboBoxCell) dr.Cells[1];
                        if(cellDbType.Value !=null)
                        {
                            for(int i=0;i<25;i++)
                            {
                                if(cellDbType.Value.ToString()== ((DbType)i).ToString())
                                {
                                    sqlParam.DbType = (DbType) i;
                                    break;
                                }
                            }
                        }

                        DataGridViewCheckBoxCell cellReturn = (DataGridViewCheckBoxCell) dr.Cells[2];
                        if(cellReturn.Value !=null)
                        {
                            bool isChecked = bool.Parse(cellReturn.Value.ToString());
                            if (isChecked)
                                sqlParam.Direction = ParameterDirection.Output;
                            else
                                sqlParam.Direction = ParameterDirection.Input;
                        }
                        else
                        {
                            sqlParam.Direction = ParameterDirection.Input;
                        }

                        sqlParam.Value = DBNull.Value;
                        DataGridViewTextBoxCell cellValue = (DataGridViewTextBoxCell) dr.Cells[3];
                        if(cellValue.Value !=null)
                        {
                            string valueStr = cellValue.Value.ToString();
                            if(!string.IsNullOrEmpty(valueStr))
                            {
                                sqlParam.Value = SqlDbTypeUtil.GetValueFromString(valueStr, sqlParam.DbType);
                            }
                        }

                        parameters.Add(sqlParam);
                    }
                }
            }

            if (parameters.Count > 0)
            {
                this._SqlComponent.Parameters = parameters.ToArray();
            }
            else
                this._SqlComponent.Parameters = null;

            this._SqlComponent.QueryOutputType = (SqlQueryOutputType) this.cbo_QueryOutputType.SelectedIndex;
            this._SqlComponent.QueryScalarOutputType =
                (SimpleDataType) this.cbo_QueryOutputDataType.SelectedIndex;
            this._SqlComponent.QueryOutputTableDefinition = null;
            if(this.dv_TableDefinition.Rows.Count>0)
            {
                DataTable tbl=new DataTable();
                foreach(DataGridViewRow dr in this.dv_TableDefinition.Rows)
                {
                    if(!dr.IsNewRow && dr.Cells[0].Value !=null)
                    {
                        string colName = ((DataGridViewTextBoxCell) dr.Cells[0]).ToString();
                        DataGridViewComboBoxCell cellDbType = (DataGridViewComboBoxCell) dr.Cells[1];
                        Type dbtype =
                            SqlDbTypeUtil.ToSystemType(SqlDbTypeUtil.GetDbType(cellDbType.Value.ToString()));
                        tbl.Columns.Add(colName, dbtype);
                    }
                }
                if(tbl.Columns.Count>0)
                    this._SqlComponent.QueryOutputTableDefinition = tbl;
            }

            // output is derived from instruction 
            SqlOutputType outputType = SqlOutputType.Nothing;
            if(this._SqlComponent.SqlCommandType==CommandType.StoredProcedure)
            {
                outputType = SqlOutputType.OutputParameters;
            }
            else if(this._SqlComponent.QueryOutputType==SqlQueryOutputType.Scalar)
            {
                outputType = SqlOutputType.Scalar;
            }
            else if(this._SqlComponent.QueryOutputType==SqlQueryOutputType.Table)
            {
                outputType = SqlOutputType.Table;
            }
            List<SimpleSqlParameter> returnParameters = new List<SimpleSqlParameter>();
            if(this._SqlComponent.Parameters !=null && this._SqlComponent.Parameters.Length>0)
            {
                foreach (SimpleSqlParameter sqlParam in this._SqlComponent.Parameters)
                {
                    if(sqlParam.Direction !=ParameterDirection.Input)
                    {
                        returnParameters.Add(sqlParam);
                    }
                }
            }
            SqlOutput output =
                new SqlOutput(this._SqlComponent.ComponentID,
                              Guid.Empty, outputType, returnParameters.ToArray(),
                              SimpleDataTypeUtil.GetNullValue(this._SqlComponent.QueryScalarOutputType),
                              this._SqlComponent.QueryOutputTableDefinition);
            this._SqlComponent.Output = output;
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            this.SaveSettings();
            if (this.UpdateComponentSettingEvent != null)
                this.UpdateComponentSettingEvent(this._OwnerActivityNode, this._SqlComponent);

            this.Close();
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void dv_Parameters_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            this.InitDbTypeComboBox();
        }

        private void cbo_CommandType_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.cbo_CommandType.SelectedIndex == 1)
            {
                this.groupBox2.Text = "Sql Parameters";
                this.p_Parameters.BringToFront();
                this.p_Parameters.Visible = true;
                this.p_Output.Visible = false;
            }
            else
            {
                this.groupBox2.Text = "Query output";
                this.p_Output.BringToFront();
                this.p_Parameters.Visible = false;
                this.p_Output.Visible = true;
            }
        }

        private void cbo_QueryOutputType_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.lbl_QueryOutputDataType.Visible = false;
            this.cbo_QueryOutputDataType.Visible = false;
            this.dv_TableDefinition.Visible = false;

            if(this.cbo_QueryOutputType.SelectedIndex==0)
            {
            }
            else if(this.cbo_QueryOutputType.SelectedIndex==1)
            {
                this.lbl_QueryOutputDataType.Visible = true;
                this.cbo_QueryOutputDataType.Visible = true;
            }
            else if(this.cbo_QueryOutputType.SelectedIndex==2)
            {
                this.dv_TableDefinition.Visible = true;
            }
        }

        private void dv_TableDefinition_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            this.InitColumnDbTypeComboBox();
        }

        private void btn_Test_Click(object sender, EventArgs e)
        {
            this.SaveSettings();

            try
            {
                List<SimpleSqlParameter> parameters = new List<SimpleSqlParameter>();
                if (this._SqlComponent.Parameters != null && this._SqlComponent.Parameters.Length > 0)
                {
                    parameters.AddRange(this._SqlComponent.Parameters);
                }
                SqlOutputType outputType = SqlOutputType.Nothing;
                if (this._SqlComponent.SqlCommandType == CommandType.StoredProcedure)
                {
                    outputType = SqlOutputType.OutputParameters;
                }
                else
                {
                    switch (this._SqlComponent.QueryOutputType)
                    {
                        case SqlQueryOutputType.Nothing:
                            outputType = SqlOutputType.Nothing;
                            break;
                        case SqlQueryOutputType.Scalar:
                            outputType = SqlOutputType.Scalar;
                            break;
                        case SqlQueryOutputType.Table:
                            outputType = SqlOutputType.Table;
                            break;
                    }
                }

                SqlWorker worker = new SqlWorker();
                object returnValue = worker.ExecuteSql(this._SqlComponent.ConnStr,
                                                       this._SqlComponent.SqlCommandText,
                                                       outputType, ref parameters,
                                                       this._SqlComponent.QueryScalarOutputType,
                                                       this._SqlComponent.QueryOutputTableDefinition);
                StringBuilder buffer = new StringBuilder();
                if (returnValue != null)
                {
                    if (returnValue.GetType() == typeof(DataTable))
                    {
                        buffer.Append("output table: " + Environment.NewLine);
                        DataTable dt = (DataTable)returnValue;
                        for (int k = 0; k < Math.Min(5, dt.Rows.Count); k++)
                        {
                            DataRow dr = dt.Rows[k];
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (dr[i] != null)
                                {
                                    buffer.Append(dr[i].ToString() + ",");
                                }
                            }
                            buffer.Append(Environment.NewLine);
                        }
                    }
                    else
                    {
                        buffer.Append("output scalar: " + returnValue.ToString());
                    }
                }

                if (parameters != null && parameters.Count > 0)
                {
                    foreach (SimpleSqlParameter sqlParam in parameters)
                    {
                        if (sqlParam.Direction != ParameterDirection.Input)
                        {
                            buffer.Append(sqlParam.ParameterName + "=");
                            if (sqlParam.Value != null && sqlParam.Value != DBNull.Value)
                            {
                                buffer.Append(sqlParam.Value.ToString());
                            }
                            buffer.Append(Environment.NewLine);
                        }
                    }
                }

                MessageBox.Show(buffer.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}